from tools import DBUtil

import pygame

class MusicService:
    def __init__(self) -> None:
        self.user = None

    def login(self,uname,passwd):
        '''登录功能'''
        # 编写sql
        sql = "select id,uname from t_user where uname=%s and passwd=%s"
        #创建数据库连接
        db = DBUtil()
        # 执行sql
        user = db.query_one(sql,uname,passwd)
        #判断用户是否存在
        if user:
            self.user = user
            return True
        else:
            return False

    def add_music(self,files:tuple[str]):
        '''添加音乐功能'''
        # 编写sql
        insert_music_sql = "insert into t_music(name,path) values(%s,%s)"
        #遍历数据
        for f in files:
            print(f)
            #获取文件名
            name = f[f.rfind('/')+1:f.rfind('.')]
            #创建工具对象
            db = DBUtil()
            #执行sql
            mid = db.execute_dml_back_id(insert_music_sql,name,f)

            #编写用户和音乐关系表的sql
            insert_play_music_sql = "insert into t_play_list(u_id,m_id) values(%s,%s)"
            #创建工具对象
            db = DBUtil()
            #执行sql
            db.execute_dml(insert_play_music_sql,self.user[0],mid)

    def find_user_music(self):
        #编写sql
        sql = "select m.name from t_play_list p left join t_music m on p.m_id = m.id where p.u_id=%s"
        #创建工具对象
        db=DBUtil()
        #执行sql
        m_list = db.query_all(sql,self.user[0])
        #返回结果
        return m_list

    def play_music(self,name):
        '''播放音乐'''
        #获取音乐的路径
        sql="select m.path from t_play_list p left join t_music m on p.m_id = m.id where p.u_id=%s and m.name=%s"
        #执行sql
        path = DBUtil().query_one(sql,self.user[0],name)[0]
        #调用播放器播放音乐
        pygame.mixer.init()
        pygame.mixer.music.load(path)
        #播放音乐
        pygame.mixer.music.play()

    def delete_music(self,name):
        '''删除音乐'''
        #编写sql,查询要删除的音乐的id
        get_id_sql = "select m.id from t_play_list p left join t_music m on p.m_id = m.id where p.u_id=%s and m.name=%s"
        #创建工具对象
        db = DBUtil()
        #执行sql
        rs = db.query_one(get_id_sql,self.user[0],name)[0]
        #编写删除音乐的sql
        delete_music_sql = "delete from t_music where id=%s"
        #编写sql,删除播放列表中的音乐
        delete_play_music_sql = "delete from t_play_list where m_id=%s and u_id=%s"
        db.execute_dml(delete_play_music_sql,rs,self.user[0])
        db.execute_dml(delete_music_sql,rs)


if __name__ == '__main__':
    service = MusicService()
    service.login('admin','123456')